#!/usr/bin/env node

const Database = require('better-sqlite3');
const db = new Database('./data/delivery-management.db');

console.log('🔍 深入分析映射不匹配的原因...\n');

console.log('1. 检查devices表中这两个设备的分类信息:');
const devices = db.prepare(`
  SELECT device_id, classification_name, specialty, classification_code
  FROM devices 
  WHERE device_id IN ('仪表-0031', '仪表-0029')
`).all();

devices.forEach(device => {
  console.log(`  ${device.device_id}: ${device.classification_name} (${device.specialty}) - ${device.classification_code}`);
});

console.log('\n2. 检查device_type_mappings表中对应的映射:');
devices.forEach(device => {
  const mappings = db.prepare(`
    SELECT classification_name, specialty, english_name, classification_code
    FROM device_type_mappings 
    WHERE classification_code = ?
  `).all(device.classification_code);
  
  console.log(`\n  ${device.device_id} (${device.classification_code}) 的映射:`);
  mappings.forEach(mapping => {
    console.log(`    - ${mapping.classification_name} (${mapping.specialty}) -> ${mapping.english_name}`);
  });
});

console.log('\n3. 检查classification_name的精确匹配:');
devices.forEach(device => {
  const exactMatch = db.prepare(`
    SELECT * FROM device_type_mappings 
    WHERE classification_name = ? AND specialty = ?
  `).all(device.classification_name, device.specialty);
  
  console.log(`\n  查找: ${device.classification_name} + ${device.specialty}`);
  console.log(`  找到匹配: ${exactMatch.length} 条`);
  
  exactMatch.forEach(match => {
    console.log(`    classification_code: ${match.classification_code}`);
    console.log(`    english_name: ${match.english_name}`);
  });
});

console.log('\n4. 检查是否存在名称差异:');
const allDeviceClasses = db.prepare(`
  SELECT DISTINCT classification_name, specialty
  FROM devices
  WHERE classification_name IS NOT NULL AND classification_name != ''
  ORDER BY specialty, classification_name
`).all();

const allMappingClasses = db.prepare(`
  SELECT DISTINCT classification_name, specialty
  FROM device_type_mappings
  WHERE classification_name IS NOT NULL AND classification_name != ''
  ORDER BY specialty, classification_name
`).all();

console.log(`\ndevices表中对象类: ${allDeviceClasses.length}`);
console.log(`mappings表中对象类: ${allMappingClasses.length}`);

// 找出在devices中但不在mappings中的
const deviceClassSet = new Set(allDeviceClasses.map(c => `${c.classification_name}|${c.specialty}`));
const mappingClassSet = new Set(allMappingClasses.map(c => `${c.classification_name}|${c.specialty}`));

const missing = [];
deviceClassSet.forEach(key => {
  if (!mappingClassSet.has(key)) {
    missing.push(key);
  }
});

console.log(`\n缺失的对象类 (${missing.length}个):`);
missing.forEach(key => {
  const [name, specialty] = key.split('|');
  console.log(`  - ${name} (${specialty})`);
});

// 检查这两个特定设备的完整映射链
console.log('\n5. 完整映射链分析:');
devices.forEach(device => {
  console.log(`\n${device.device_id}:`);
  console.log(`  devices表: ${device.classification_name} (${device.specialty})`);
  
  // 通过classification_code查找
  const byCodeMappings = db.prepare(`
    SELECT * FROM device_type_mappings WHERE classification_code = ?
  `).all(device.classification_code);
  
  console.log(`  通过分类代码找到 ${byCodeMappings.length} 条映射:`);
  byCodeMappings.forEach(mapping => {
    console.log(`    mapping表: ${mapping.classification_name} (${mapping.specialty}) -> ${mapping.english_name}`);
    
    // 检查名称是否完全匹配
    const nameMatch = mapping.classification_name === device.classification_name;
    const specialtyMatch = mapping.specialty === device.specialty;
    console.log(`    名称匹配: ${nameMatch}, 专业匹配: ${specialtyMatch}`);
  });
});

db.close();